CREATE RULE
CREATE RULE — Define a new rewrite rule
Synopsis
CREATE [ OR REPLACE ] RULE name AS ON event
TO table_name [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
where event can be one of:
SELECT | INSERT | UPDATE | DELETE
Description
CREATE RULE defines a new rule applying to a specified table or view. CREATE OR REPLACE RULE will create a new rule or replace an existing rule with the same name on the same table.
The rule system allows us to define alternate actions for insert, update, or delete operations on database tables. Roughly speaking, when a given command is executed on a given table, a rule causes additional commands to be executed. Alternatively, an INSTEAD rule can replace a given command with another command, or cause a command to not be executed at all. Rules are also used to implement SQL views. Rules are essentially a command transformation mechanism or command macro. This transformation takes place before command execution begins. If you actually want to trigger an operation for each physical row independently, you probably need a trigger rather than a rule.
Currently, ON SELECT rules must be unconditional INSTEAD rules, and their action must consist of a single SELECT command. Therefore, an ON SELECT rule effectively turns a table into a view whose visible content is returned by the rule's SELECT command, rather than content stored directly in the table (if any). However, it is still better to use the CREATE VIEW command than to create a real table and define an ON SELECT rule on it.
Updatable views can be created by defining ON INSERT, ON UPDATE, and ON DELETE rules (or any subset of these rules), which can replace update actions on the view with appropriate update actions on other tables. If you want to support INSERT RETURNING and so on, you must include an appropriate RETURNING clause in each such rule.
If you attempt to use conditional rules for complex view updates, it is important to note that there must be an INSTEAD rule for every action you wish to allow on the view. If the rule is conditional, or is not INSTEAD, the system will still refuse to attempt to execute the update action, because it considers that in some cases it should stop trying to execute actions on the view's dummy table. If you want to handle all useful cases in conditional rules, you can add an unconditional DO INSTEAD NOTHING rule to ensure the system understands it will never be called upon to update the dummy table. Then make the conditional rules non-INSTEAD. When they apply, they will be added to the default INSTEAD NOTHING action (however, this approach currently does not support RETURNING queries).
Parameters
name
The name of the rule to be created. It must be distinct from the names of any other rules on the same table. Multiple rules for the same event type on the same table are applied in alphabetical order by name.
event
The event is one of SELECT, INSERT, UPDATE, or DELETE. Note that INSERT containing an ON CONFLICT clause cannot be used on a table with INSERT or UPDATE rules. In that case, consider using an updatable view.
table_name
The name of the table or view to which the rule applies (can be schema-qualified).
condition
An arbitrary SQL conditional expression (returning boolean). The conditional expression cannot reference any tables other than NEW and OLD, and cannot contain aggregate functions.
INSTEAD
INSTEAD indicates that the command should be executed instead of the original command.
ALSO
ALSO indicates that the commands should be executed in addition to the original command.
If neither ALSO nor INSTEAD is specified, the default is ALSO.
command
The command(s) constituting the rule action. Available commands are SELECT, INSERT, UPDATE, DELETE, and NOTIFY.
In condition and command, the table names NEW and OLD can be used to reference values in the referenced table. In ON INSERT and ON UPDATE rules, NEW is used to reference the new row being inserted or updated. In ON UPDATE and ON DELETE rules, OLD is used to reference the existing row being updated or deleted.
Notes
To create or modify rules on a table, you must be the owner of the table.
In a rule for INSERT, UPDATE, or DELETE on a view, you can add a RETURNING clause to emit the view's columns. If the rule is triggered by an INSERT RETURNING, UPDATE RETURNING, or DELETE RETURNING command, this clause will be used to compute the output. When the rule is triggered by a command without RETURNING, the rule's RETURNING clause will be ignored. The current implementation only allows unconditional INSTEAD rules to contain RETURNING. Furthermore, at most one RETURNING clause is allowed among all rules for the same event (this ensures that only one candidate RETURNING clause is used to compute the result). If no RETURNING clause exists in any applicable rule, RETURNING queries on the view will be rejected. It is very important to avoid circular rules. For example, although both rule definitions below are accepted by the system, the SELECT command will cause the system to report an error because a recursive rule would be produced:
CREATE RULE "_RETURN" AS
ON SELECT TO t1
DO INSTEAD
SELECT * FROM t2;
CREATE RULE
CREATE RULE "_RETURN" AS
ON SELECT TO t2
DO INSTEAD
SELECT * FROM t1;
SELECT * FROM t1;
# Currently, if a rule action contains a NOTIFY command, the NOTIFY command will be executed unconditionally, that is, even if the rule does not apply to any rows, the NOTIFY will still be issued. For example, in:
CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;
UPDATE mytable SET name = 'foo' WHERE id = 42;
# In the above statements, a NOTIFY event will be issued during the UPDATE, regardless of whether any rows match the condition id = 42. This is an implementation limitation that may be fixed in a future release.